﻿Imports PhDir.DAL
Imports System.Data
Imports System.Data.SqlClient

Namespace DAL

    Public Class DbConnection

        Implements IDisposable

        Friend Const NoChanges = 0

        Sub New(ByVal contextName As String)
            Me.SqlConnection = New SqlConnection(DbConfig.GetConnectionString(contextName))
            Me.SqlCommand = New SqlCommand With {.Connection = Me.SqlConnection}
        End Sub

        Sub Dispose() Implements IDisposable.Dispose
            If Me.SqlConnection IsNot Nothing Then
                Me.SqlConnection.Dispose()
            End If
            If Me.SqlCommand IsNot Nothing Then
                Me.SqlCommand.Dispose()
            End If
            If Me.SqlDataReader IsNot Nothing Then
                Me.SqlDataReader.Dispose()
            End If
        End Sub

        Private Property SqlConnection As SqlConnection
        Private Property SqlCommand As SqlCommand
        Private Property SqlDataReader As SqlDataReader

        Friend WriteOnly Property CommandText As String
            Set(value As String)
                Me.SqlCommand.CommandText = value
            End Set
        End Property

        Friend WriteOnly Property CommandType As CommandType
            Set(value As CommandType)
                Me.SqlCommand.CommandType = value
            End Set
        End Property

        Friend Sub AddParameter(ByVal parameterName As String, ByVal parameterValue As Object)
            Dim sqlParameter = New SqlParameter(parameterName, parameterValue)
            Me.SqlCommand.Parameters.Add(sqlParameter)
        End Sub

        Protected Sub OpenConnection()
            If (Me.SqlConnection.State = ConnectionState.Closed) Then
                Me.SqlConnection.Open()
            End If
        End Sub

        Protected Sub CloseConnection()
            If (Me.SqlConnection.State = ConnectionState.Closed) Then
                Me.SqlConnection.Close()
            End If
        End Sub

        ''' <summary>
        ''' Either Executing CREATE, UPDATE OR DELETE
        ''' </summary>
        ''' <returns>
        ''' True, if number of rows affected > 0. 
        ''' Otherwise, false, if number of rows affected = 0.
        ''' </returns>
        ''' <remarks></remarks>
        Friend Function ExecuteNonQuery() As Boolean
            Try
                Me.OpenConnection()

                Dim affectedRow = Me.SqlCommand.ExecuteNonQuery()

                If affectedRow > NoChanges Then
                    Return True
                End If

                Return False
            Catch sqlException As SqlException
                Debug.Fail(sqlException.Message)
                Console.WriteLine(sqlException.Message)
                Return False
            Catch invalidOperationException As InvalidOperationException
                Debug.Fail(invalidOperationException.Message)
                Console.WriteLine(invalidOperationException.Message)
                Return False
            Catch ex As Exception
                Debug.Fail(ex.Message)
                Console.WriteLine(ex.Message)
                Return False
            End Try
        End Function 'ExecuteNonQuery() As Boolean

        ''' <summary>
        ''' Retrieve the data from database. In other words, use this
        ''' method for SELECT command
        ''' </summary>
        ''' <returns>
        ''' Returns new SqlDataReader object if successfully executed.
        ''' Otherwise, return null/Nothing (Exception occurred)
        ''' </returns>
        ''' <remarks></remarks>
        Friend Function ExecuteReader() As SqlDataReader
            Try
                Me.OpenConnection()

                'Execute the Stored Procedures
                Return Me.SqlCommand.ExecuteReader()
            Catch sqlException As SqlException
                Debug.Fail(sqlException.Message)
                Console.WriteLine(sqlException.Message)
                Return Nothing
            Catch invalidOperationException As InvalidOperationException
                Debug.Fail(invalidOperationException.Message)
                Console.WriteLine(invalidOperationException.Message)
                Return Nothing
            Catch ex As Exception
                Debug.Fail(ex.Message)
                Console.WriteLine(ex.Message)
                Return Nothing
            End Try
        End Function 'ExecuteReader() As SqlDataReader

        ''' <summary>
        ''' Executes query and returns the first column of the first row 
        ''' in the result set returned by the query. Additional columns or 
        ''' rows are ignored.
        ''' </summary>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Friend Function ExecuteScalar() As Object
            Try
                Me.OpenConnection()

                Return Me.SqlCommand.ExecuteScalar()
            Catch sqlException As SqlException
                Debug.Fail(sqlException.Message)
                Console.WriteLine(sqlException.Message)
                Return Nothing
            Catch invalidOperationException As InvalidOperationException
                Debug.Fail(invalidOperationException.Message)
                Console.WriteLine(invalidOperationException.Message)
                Return Nothing
            Catch ex As Exception
                Debug.Fail(ex.Message)
                Console.WriteLine(ex.Message)
                Return Nothing
            End Try
        End Function 'ExecuteScalar() As Object

    End Class

End Namespace
